package com.inibb.app.service.admin.impl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.inibb.app.service.admin.DBAdminService;



@Service
@Transactional
public class DBAdminServiceImpl implements DBAdminService{
	
	private JdbcTemplate simpleJdbcTemplate;
	@Autowired
	private DataSource dataSource;
	public void setDataSource(DataSource ds){
		this.dataSource = ds;
	}

	public JdbcTemplate getSimpleJdbcTemplate() {
		if (simpleJdbcTemplate == null)
			simpleJdbcTemplate = new JdbcTemplate(dataSource);
		return simpleJdbcTemplate;
	}
	
	@Override
	public String createAlterDrop(String sql) {
		try{
		this.getSimpleJdbcTemplate().execute(sql);
		}catch(Exception e){
			return e.getMessage();
		}
		return "";
	}

	@Override
	public String insertDeleteUpdate(String sql) {
		sql = sql.trim();
		try{
		int rows=this.getSimpleJdbcTemplate().update(sql);
		return rows+" rows affected";
		}catch(Exception e){
			return e.getMessage();
		}
	}

	@Override
	public List<List<String>> select(String sql) {
		sql = sql.trim();
		List<Map<String, Object>> list=null;
		List<List<String>> results=new ArrayList<List<String>>();
		ArrayList<String> desc=new ArrayList<String>();
		try{
		list = this.getSimpleJdbcTemplate().queryForList(sql);
		}catch(Exception e){
			desc.add(e.getMessage());
			results.add(desc);
			return results;
		}
		
		desc.add(sql);
		results.add(desc);
		boolean firstRow=true;
		Set header=null;
		for(Object o:list){
			
			Map<String, Object> m = (Map)o;
			if(firstRow){
				header=m.keySet();
				//Add Header
				Iterator it=header.iterator();
				ArrayList<String> oneLine=new ArrayList<String>();
				while(it.hasNext()){
					oneLine.add(StringUtils.trim(it.next().toString()));
				}
				results.add(oneLine);
				firstRow=false;
				//Add First Row
				ArrayList<String> secondLine=new ArrayList<String>();
				Iterator itSec=header.iterator();
				while(itSec.hasNext()){
					secondLine.add(StringUtils.trim(m.get(StringUtils.trim(itSec.next().toString())).toString()));
				}
				results.add(secondLine);
			}else{
				ArrayList<String> oneLine=new ArrayList<String>();
				Iterator it=header.iterator();
				while(it.hasNext()){
					oneLine.add(StringUtils.trim(m.get(StringUtils.trim(it.next().toString())).toString()));
				}
				results.add(oneLine);
			}
		}
		return results;
	}
}
